SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[tvf_Generic_GetSpecificNAVDatePriceTBL]
(
	-- Add the parameters for the function here
	@PriceDate DATE , @NAVDateOffsetNum INT
	
	)
RETURNS   @tmp TABLE (
LXID VARCHAR(50), NAVDate DATE, Bid DECIMAL(38,10), Mid DECIMAL(38,10), Ask DECIMAL(38,10), Volume VARCHAR(50))
AS
BEGIN
	
--	DECLARE @ExportId  INT = (SELECT MAX(e.ExportId) FROM FSFundModel.Reporting.Export e WHERE e.FundId ='FSIC') 
--	DECLARE @LXID VARCHAR(50) ='LX118871' 
--	 DECLARE @Field VARCHAR(MAX) = 'Mid'
--	DECLARE @PriceDate DATETIME	 = (
	
--	SELECT TOP 1 p.PriceDate FROM FSFundModel.Reporting.Pricing p 
--	WHERE p.PricingId =  @LXID
--	AND p.ExportId = @ExportId)

----SELECT @PriceDate
	
	DECLARE @ShortNAVDate AS DATE = @PriceDate 
--    --SELECT @ShortNAVDate , @PriceDate
--  SET @ShortNAVDate = @PriceDate
  
  DECLARE @TVFD AS DATE  = (SELECT  NAVDate FROM ReportingScripts.dbo.tvfNAVDatesByFund(@PriceDate, @NAVDateOffsetNUM,0,0) )
	-- Add the T-SQL statements to compute the return value here
INSERT INTO @tmp
        ( LXID , NAVDate , Mid, Bid, Ask , Volume )

SELECT 
		prc.PricingId, 
		@TVFD,

		 prc.Mid		'Mid' 
,		 prc.Bid		'Bid' 
,		 prc.Offer		'Ask' 
,		prc.depth 'Depth'
	
		
	 FROM FSFundModel.dbo.Pricing prc 
--RIGHT JOIN FSPricing.dbo.Pricing_MarkitLoans_Marks pDB_ML
--	 ON pDB_ML.LoanXID=prc.PricingId


	 

		WHERE prc.PriceDate =  @TVFD
		--AND pDB_ML.MarkDate = @TVFD	
		AND prc.PriceDate IS NOT NULL 

	--	AND LTRIM(RTRIM(prc.PricingId)) = LTRIM(RTRIM(@LXID))
		--AND prc.ExportId =  @ExportId 
		
		RETURN
        



END
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'dbo', 'FUNCTION', N'tvf_Generic_GetSpecificNAVDatePriceTBL', NULL, NULL
GO
